Introduction

A union injection is a type of in-band SQL injection which allows for the extraction of data by appending the results of an additional malicious query to that of the original one. Apart from the fact that the query's output must be returned on the response page, there are two additional conditions that must to be satisfied:

  • The malicious query must return the exact same number of columns as the original query.
  • The data types of the respective columns of the two queries must be compatible with one another.

Example: Union Injection

We can show a union injection using this PortSwigger lab. We are told that the database has a table called users and that the query returns two columns.

We can guess the column names in the users table and use the following payload to obtain the results:

' UNION SELECT username, password FROM users -- -

Determining the Number of Columns

The number of columns in the injected query must match the number of columns in the original query. However, it is rarely immediately obvious what this number is.

One way to determine the number of columns in the original query is to inject a series of ORDER BY statements:

' ORDER BY 1 -- -
' ORDER BY 2 -- -
' ORDER BY 3 -- -
...

These payloads order the results of the original query by different columns. When the specified column index exceeds the number of actual columns in the original query, an error is returned. This means that the last valid index represents the number of columns returned by the query.

Another way to determine the number of columns is by using a series of SELECT NULL statements:

' UNION SELECT NULL -- -
' UNION SELECT NULL, NULL -- -
' UNION SELECT NULL, NULL, NULL -- -
...

If the number of NULLs does not match the number of columns, the database will return an error. Once the error is gone, we know how many columns are returned by the query. We use the NULL type because it can be converted to every common data type and so we need not worry about errors arising from type mismatches.

Note

In both scenarios, the application may return a verbose database error, a generic error or simply exhibit a change in behaviour, so one should be on the lookout for all three.

Determining the Data Type of a Column

Once the number of columns has been determined, one can look for columns that contain entries of a specific data type. To determine the data type of a specific column, one can just replace the NULL value corresponding to it with a random value of the desired data type.

Test for string:

' UNION SELECT NULL, 'random string', NULL, -- -

Test for integer

' UNION SELECT NULL, 12, NULL -- -